import flask
import psycopg2


app = flask.Flask(__name__)
conn = psycopg2.connect(host="10.186.9.179", database="njt_wms", user="postgres", password="pg#sub.123123")
cur = conn.cursor()


@app.route('/order-statistics', methods=['GET'])
def get_order_statistics_list():
  cur.execute('''
  SELECT A
   	.wh_id, a.wh_name,
   	COUNT ( b.order_line_id ) AS init_line_count
  FROM
   	w_b_wh
   	A LEFT JOIN (
   	SELECT
      		orders.wh_id,
      		orderline.order_line_id
   	FROM
      		w_o_order orders
      		LEFT JOIN w_o_order_line orderline ON orders.order_id = orderline.order_id
   	WHERE
      		DATE ( orderline.create_dt ) = CURRENT_DATE
      		AND ( orderline.order_line_state = '0' OR orderline.order_line_state = '11' )
   	) b ON A.wh_id = b.wh_id
GROUP BY
   	A.wh_id;
''')
  lines = cur.fetchall()
  print(lines)
  response = []
  if len(lines) > 0:
    for each in lines:
      item_dict = {'whId': each[0], 'whName': each[1], 'initLineCount': each[2], 'caseToPicking': None,
                   'caseToQc': None, 'caseToQcFinish': None, 'caseToConsolidated': None,
                   'caseToConsigneeReceived': None, 'packState': None, 'cnt': None}
      cur.execute(
        f"SELECT count(1) from (SELECT wh_id, case_id FROM w_o_pick_line WHERE Date(allocated_dt) = CURRENT_DATE and pick_state = '0' AND wh_id = {each[0]} GROUP BY case_id, wh_id) as A GROUP BY wh_id;")
      caseToPicking = cur.fetchone()
      if caseToPicking is not None:
        item_dict['caseToPicking'] = caseToPicking[0]
      # 待Qc箱数 已拣货 + 已贴标的箱数
      sqlToCaseToQc = f"SELECT count(1) from (SELECT wh_id, case_id FROM w_o_pick_line WHERE Date(allocated_dt) = CURRENT_DATE and pick_state = '22' AND wh_id = {each[0]} GROUP BY case_id, wh_id) as A GROUP BY wh_id;"
      # w_o_pack_line
      sqlToPackLine = f"SELECT count(case_id) as cnt, pack_state FROM w_o_pack WHERE Date(create_dt) = CURRENT_DATE AND wh_id = {each[0]} GROUP BY pack_state "
      cur.execute(sqlToPackLine)
      dataForPackLine = cur.fetchall()
      for item in dataForPackLine:
        cur.execute(sqlToCaseToQc)
        sqlToCaseToQcResp = cur.fetchone()
        if sqlToCaseToQcResp is not None:
          case_to_qc = sqlToCaseToQcResp[0]
        else:
          case_to_qc = 0
        if item[1] == '33':
          item_dict['caseToQc'] = item[0] + case_to_qc
        elif item[1] == '35':
          item_dict['CaseToQcFinish'] = item[0]
        elif item[1] == '37':
          item_dict['CaseToConsolidated'] = item[0]
        elif item[1] == '93':
          item_dict['CaseToConsigneeReceived'] = item[0]
      response.append(item_dict)
    return response
  else:
    return 'No order statistics'


if __name__ == '__main__':
  app.run(port=2024, host="127.0.0.1", debug=True)
